package com.x.erp.dao;

import com.x.erp.model.CarCheckVO;
import com.x.framework.dao.BaseDao;
import org.springframework.stereotype.Repository;

import java.util.ArrayList;
import java.util.List;

@Repository
public class CarCheckDao extends BaseDao {
	private final static String selectCarCheckListSql = "SELECT " +
			"eclFirst.CAR_LOG_INFO AS CAR_LOG_INFO_FIRST, " +
			"eclFirst.CAR_LOG_CREATE_TIME AS CAR_LOG_CREATE_TIME_FIRST, " +
			"eclFirst.CAR_STATE_KEY AS CAR_STATE_KEY_FIRST, " +
			"eclFirst.CAR_STATE_VALUE AS CAR_STATE_VALUE_FIRST, " +
			"eclFirst.CAR_LOG_ID AS CAR_LOG_ID_FIRST, " +
			"eu2.USER_NAME AS USER_NAME_FIRST, " +
			"eu2.USER_ID AS USER_ID_FIRST, " +
			"eclLast.CAR_LOG_INFO AS CAR_LOG_INFO_LAST, " +
			"eclLast.CAR_LOG_CREATE_TIME AS CAR_LOG_CREATE_TIME_LAST, " +
			"eclLast.CAR_STATE_KEY AS CAR_STATE_KEY_LAST, " +
			"eclLast.CAR_STATE_VALUE AS CAR_STATE_VALUE_LAST, " +
			"eclLast.CAR_LOG_ID AS CAR_LOG_ID_LAST, " +
			"eu1.USER_NAME AS USER_NAME_LAST, " +
			"eu1.USER_ID AS USER_ID_LAST, " +
			"ec.CAR_ID, " +
			"ec.CAR_VIN, " +
			"ec.CAR_IMAGE_URL, " +
			"ec.CAR_TITLE, " +
			"ec.CAR_DETECT_STATE, " +
			"ec.CAR_SALE_STATE, " +
			"ec.CAR_STOCK_STATE, " +
			"ec.CAR_AUTH_STATE, " +
			"ec.CAR_PLEDGE_STATE, " +
			"ec.CAR_SITE_STATE, " +
			"ec.CAR_ORDER_STATE, " +
			"bb.BRAND_NAME, " +
			"bb.BRAND_ID, " +
			"bs.SERIES_NAME, " +
			"bs.SERIES_ID, " +
			"bm.MODEL_NAME, " +
			"bm.MODEL_ID, " +
			"em.MARKET_NAME, " +
			"em.MARKET_ID, " +
			"es.SHOP_NAME, " +
			"es.SHOP_ID, " +
			"bc.CITY_NAME, " +
			"bc.CITY_ID, " +
			"bp.PROVINCE_NAME, " +
			"bp.PROVINCE_ID, " +
			"eu3.MARKET_ID AS USER_MARKET_ID, " +
			"eu3.SHOP_ID AS USER_SHOP_ID " +
			"FROM " +
			"ERP_CAR ec " +
			"JOIN x_base.BASE_BRAND bb ON ec.BRAND_ID = bb.BRAND_ID " +
			"JOIN x_base.BASE_SERIES bs ON ec.SERIES_ID = bs.SERIES_ID " +
			"JOIN x_base.BASE_MODEL bm ON ec.MODEL_ID = bm.MODEL_ID " +
			"JOIN ERP_MARKET em ON ec.MARKET_ID = em.MARKET_ID " +
			"JOIN ERP_SHOP es ON ec.SHOP_ID = es.SHOP_ID " +
			"LEFT JOIN (SELECT MAX(ecl1.CAR_LOG_ID) AS CAR_LOG_ID, ecl1.CAR_ID FROM ERP_CAR_LOG ecl1 WHERE ecl1.CAR_STATE_KEY = 'carStockState' AND ecl1.CAR_STATE_VALUE in(1,20) GROUP BY ecl1.CAR_ID) ecl2 ON ec.CAR_ID = ecl2.CAR_ID " +
			"LEFT JOIN ERP_CAR_LOG eclLast ON eclLast.CAR_LOG_ID = ecl2.CAR_LOG_ID " +
			"LEFT JOIN ERP_USER eu1 ON eclLast.USER_ID = eu1.USER_ID " +
			"LEFT JOIN (SELECT MIN(ecl1.CAR_LOG_ID) AS CAR_LOG_ID, ecl1.CAR_ID FROM ERP_CAR_LOG ecl1 WHERE ecl1.CAR_STATE_KEY = 'carStockState' AND ecl1.CAR_STATE_VALUE in (10,11) GROUP BY ecl1.CAR_ID ) ecl3 ON ec.CAR_ID = ecl3.CAR_ID " +
			"LEFT JOIN ERP_CAR_LOG eclFirst ON eclFirst.CAR_LOG_ID = ecl3.CAR_LOG_ID " +
			"LEFT JOIN ERP_USER eu2 ON eclFirst.USER_ID = eu2.USER_ID " +
			"LEFT JOIN BASE_CITY bc ON bc.CITY_ID = em.CITY_ID " +
			"LEFT JOIN BASE_PROVINCE bp ON bp.PROVINCE_ID = em.PROVINCE_ID " +
			"LEFT JOIN ERP_USER eu3 ON ec.USER_ID = eu3.USER_ID " +
			"WHERE 1=1 ";

	/**
	 * 查询未入库车辆
	 * @param carCheckVO
	 * @return
	 * @throws Exception
	 */
	public List<CarCheckVO> selectCarCheckList(CarCheckVO carCheckVO) throws Exception{
		StringBuilder sql = new StringBuilder(selectCarCheckListSql);
		List<Object> params = new ArrayList<>();
		sql.append("AND ec.CAR_STOCK_STATE != 0 ");
		searchCondition(sql, params, carCheckVO);
		sql.append("ORDER BY eclFirst.CAR_LOG_CREATE_TIME DESC ");
		sql = new StringBuilder("SELECT * FROM (").append(sql).append(") carCheckVo ");
		return this.queryForList(sql.toString(), CarCheckVO.class, params, carCheckVO);
	}

	private final static String selectCarOnStockNumSql = "SELECT " +
			"COUNT(ec.CAR_ID) " +
			"FROM " +
			"ERP_CAR ec " +
			"JOIN x_base.BASE_BRAND bb ON ec.BRAND_ID = bb.BRAND_ID " +
			"JOIN x_base.BASE_SERIES bs ON ec.SERIES_ID = bs.SERIES_ID " +
			"JOIN x_base.BASE_MODEL bm ON ec.MODEL_ID = bm.MODEL_ID " +
			"JOIN ERP_MARKET em ON ec.MARKET_ID = em.MARKET_ID " +
			"JOIN ERP_SHOP es ON ec.SHOP_ID = es.SHOP_ID " +
			"LEFT JOIN (SELECT MAX(ecl1.CAR_LOG_ID) AS CAR_LOG_ID, ecl1.CAR_ID FROM ERP_CAR_LOG ecl1 WHERE ecl1.CAR_STATE_KEY = 'carStockState' AND ecl1.CAR_STATE_VALUE in(1,20) GROUP BY ecl1.CAR_ID) ecl2 ON ec.CAR_ID = ecl2.CAR_ID " +
			"LEFT JOIN ERP_CAR_LOG eclLast ON eclLast.CAR_LOG_ID = ecl2.CAR_LOG_ID " +
			"LEFT JOIN ERP_USER eu1 ON eclLast.USER_ID = eu1.USER_ID " +
			"LEFT JOIN (SELECT MIN(ecl1.CAR_LOG_ID) AS CAR_LOG_ID, ecl1.CAR_ID FROM ERP_CAR_LOG ecl1 WHERE ecl1.CAR_STATE_KEY = 'carStockState' AND ecl1.CAR_STATE_VALUE = 10 GROUP BY ecl1.CAR_ID ) ecl3 ON ec.CAR_ID = ecl3.CAR_ID " +
			"LEFT JOIN ERP_CAR_LOG eclFirst ON eclFirst.CAR_LOG_ID = ecl3.CAR_LOG_ID " +
			"LEFT JOIN ERP_USER eu2 ON eclFirst.USER_ID = eu2.USER_ID " +
			"LEFT JOIN BASE_CITY bc ON bc.CITY_ID = em.CITY_ID " +
			"LEFT JOIN BASE_PROVINCE bp ON bp.PROVINCE_ID = em.PROVINCE_ID " +
			"LEFT JOIN ERP_USER eu3 ON ec.USER_ID = eu3.USER_ID " +
			"WHERE 1=1 ";
	/**
	 * 查询未入库车辆总数
	 * @param carCheckVO
	 * @return
	 */
	public int selectCarCheckNum(CarCheckVO carCheckVO) {
		StringBuilder sql = new StringBuilder(selectCarOnStockNumSql);
		List<Object> params = new ArrayList<>();
		sql.append("AND ec.CAR_STOCK_STATE != 0 ");
		searchCondition(sql, params, carCheckVO);
		return this.queryForInt(sql.toString(), params);
	}

	private void searchCondition(StringBuilder sql, List<Object> params, CarCheckVO carCheckVO) {
		if(carCheckVO != null){
			if(this.isString(carCheckVO.getCarId())){
				sql.append("AND ec.CAR_ID = ? ");
				params.add(carCheckVO.getCarId());
			}
			if(this.isString(carCheckVO.getCarVin())){
				sql.append("AND ec.CAR_VIN = ? ");
				params.add(carCheckVO.getCarVin());
			}
			if(this.isString(carCheckVO.getCarVin6())){
				sql.append("AND ec.CAR_VIN6 = ? ");
				params.add(carCheckVO.getCarVin6());
			}
			if(this.isString(carCheckVO.getProvinceId())){
				sql.append("AND em.PROVINCE_ID = ? ");
				params.add(carCheckVO.getProvinceId());
			}
			if(this.isString(carCheckVO.getCityId())){
				sql.append("AND em.CITY_ID = ? ");
				params.add(carCheckVO.getCityId());
			}
			if(this.isString(carCheckVO.getMarketId())){
				sql.append("AND ec.MARKET_ID = ? ");
				params.add(carCheckVO.getMarketId());
			}
			if(this.isString(carCheckVO.getShopId())){
				sql.append("AND ec.SHOP_ID = ? ");
				params.add(carCheckVO.getShopId());
			}
			if(carCheckVO.getCarOrderState() != null){
				sql.append("AND ec.CAR_ORDER_STATE = ? ");
				params.add(carCheckVO.getCarOrderState());
			}
			if(carCheckVO.getCarSaleState() != null){
				sql.append("AND ec.CAR_SALE_STATE = ? ");
				params.add(carCheckVO.getCarSaleState());
			}
			if(carCheckVO.getCarDetectState() != null){
				sql.append("AND ec.CAR_DETECT_STATE = ? ");
				params.add(carCheckVO.getCarDetectState());
			}
			Integer carStockState = carCheckVO.getCarStockState();
			if(carStockState != null){
				// 根据库存状态查询车辆信息，查询的是整十以内的数据，如传入10查询[10,20)的数据
				if(carStockState % 10 == 0) {
					sql.append("AND ec.CAR_STOCK_STATE >= ? AND ec.CAR_STOCK_STATE < ? ");
					params.add(carStockState);
					params.add(carStockState + 10);
				}else{
					sql.append("AND ec.CAR_STOCK_STATE = ? ");
					params.add(carStockState);
				}
			}
			if(carCheckVO.getCarAuthState() != null){
				sql.append("AND ec.CAR_AUTH_STATE = ? ");
				params.add(carCheckVO.getCarAuthState());
			}
			if(this.isString(carCheckVO.getCarRfid())){
				sql.append("AND ec.CAR_RFID = ? ");
				params.add(carCheckVO.getCarRfid());
			}
			if(carCheckVO.getBrandId() != null){
				sql.append("AND ec.BRAND_ID = ? ");
				params.add(carCheckVO.getBrandId());
			}
			if(carCheckVO.getSeriesId() != null){
				sql.append("AND ec.SERIES_ID = ? ");
				params.add(carCheckVO.getSeriesId());
			}
			if(carCheckVO.getModelId() != null){
				sql.append("AND ec.MODEL_ID = ? ");
				params.add(carCheckVO.getModelId());
			}
			if(carCheckVO.getCarPledgeState() != null){
				sql.append("AND ec.CAR_PLEDGE_STATE = ? ");
				params.add(carCheckVO.getCarPledgeState());
			}
			if(carCheckVO.getCarSiteState() != null){
				sql.append("AND ec.CAR_SITE_STATE = ? ");
				params.add(carCheckVO.getCarSiteState());
			}
			if(this.isString(carCheckVO.getCarLogIdFirst())){
				sql.append("AND eclFirst.CAR_LOG_ID = ? ");
				params.add(carCheckVO.getCarLogIdFirst());
			}
			if(this.isString(carCheckVO.getCarLogIdLast())){
				sql.append("AND eclLast.CAR_LOG_ID = ? ");
				params.add(carCheckVO.getCarLogIdLast());
			}
			if(this.isString(carCheckVO.getCarLogInfoFirst())){
				sql.append("AND eclFirst.CAR_LOG_INFO = ? ");
				params.add(carCheckVO.getCarLogInfoFirst());
			}
			if(this.isString(carCheckVO.getCarLogInfoLast())){
				sql.append("AND eclLast.CAR_LOG_INFO = ? ");
				params.add(carCheckVO.getCarLogInfoLast());
			}
			if(this.isString(carCheckVO.getCarStateKeyFirst())){
				sql.append("AND eclFirst.CAR_STATE_KEY = ? ");
				params.add(carCheckVO.getCarStateKeyFirst());
			}
			if(this.isString(carCheckVO.getCarStateKeyLast())){
				sql.append("AND eclLast.CAR_STATE_KEY = ? ");
				params.add(carCheckVO.getCarStateKeyLast());
			}
			if(carCheckVO.getCarStateValueFirst() != null){
				sql.append("AND eclFirst.CAR_STATE_VALUE = ? ");
				params.add(carCheckVO.getCarStateValueFirst());
			}
			if(carCheckVO.getCarStateValueLast() != null){
				sql.append("AND eclLast.CAR_STATE_VALUE = ? ");
				params.add(carCheckVO.getCarStateValueLast());
			}
			if(carCheckVO.getStartTimeFirst() != null){
				sql.append("AND eclFirst.CAR_LOG_CREATE_TIME >= ? ");
				params.add(carCheckVO.getStartTimeFirst());
			}
			if(carCheckVO.getEndTimeFirst() != null){
				sql.append("AND eclFirst.CAR_LOG_CREATE_TIME < ? ");
				params.add(carCheckVO.getEndTimeFirst());
			}
			if(carCheckVO.getStartTimeLast() != null){
				sql.append("AND eclLast.CAR_LOG_CREATE_TIME >= ? ");
				params.add(carCheckVO.getStartTimeLast());
			}
			if(carCheckVO.getEndTimeLast() != null){
				sql.append("AND eclLast.CAR_LOG_CREATE_TIME < ? ");
				params.add(carCheckVO.getEndTimeLast());
			}
		}
	}

}
